package com.junzhiit.nengji.dao.impl;

import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.bo.ChainGoodsBO;
import com.junzhiit.nengji.bo.ChainGoodsListBO;
import com.junzhiit.nengji.dao.ChainGoodsDAO;
import com.junzhiit.nengji.model.ChainGoods;
import com.junzhiit.nengji.model.enums.ChainGoodsStatus;
import com.junzhiit.nengji.model.enums.GoodsImagesType;
import com.junzhiit.nengji.model.enums.GoodsType;
import com.junzhiit.nengji.model.enums.Is;

@Repository
public class ChainGoodsDAOImpl extends AbstractBaseDAO<ChainGoods> implements ChainGoodsDAO{
	
	@Override
	public List<ChainGoodsBO> findPageAll(ChainGoods chainGoods, int pageNumber, int maxPageRow,Integer checkType) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" cg.`audit_fail_description`,cg.`code`,cg.updated_time,cg.id,cg.`name`,cg.price,cg.reference_price,cg.is_open_sku,cg.is_open_member,cg.is_free_shipping,cg.`status`,cg.is_recommend,cg.is_open_sell,cg.is_open_rebate,cg.profit,cg.postage_type,cg.postage_price,cg.type,cg.is_help_peasant,");
		sql.append(" cg.integral,");
		//stock by sku
		sql.append(" (case cg.is_open_sku ");
		sql.append(" when 0 then cg.stock ");
		sql.append(" when 1 then (select sum(csc.stock) from chain_sku_content csc where csc.chain_goods_id=cg.id)");
		sql.append(" end)");
		sql.append(" stock,");
		
		//price by sku
		sql.append(" (case cg.is_open_sku ");
		sql.append(" when 1 then (select case min(csc.price)=max(csc.price) when 0 then concat_ws('~',min(csc.price),max(csc.price)) when 1 then min(csc.price)  end  from chain_sku_content csc where csc.chain_goods_id=cg.id) ");
		sql.append(" end)");
		sql.append(" sku_range_price,");
		
		
		sql.append(" cb.`name` brand_name, ");
		sql.append(" ct.`name` type_name, ");
		sql.append(" c.`name` chain_name, ");
		sql.append(" cgi.file_name, ");
		//systemType concat
		sql.append(" (select group_concat(st.`name`)  from chain_goods_system_type cgst inner join system_type st on cgst.system_type_id=st.id  where cgst.chain_goods_id=cg.id) systemType ");
		sql.append("  from chain_goods cg ");
		sql.append(" left join chain_brand cb on cg.chain_brand_id=cb.id ");
		sql.append(" left join chain_type ct on cg.chain_type_id=ct.id ");
		
		sql.append(" left join chain_goods_images cgi on cg.id=cgi.chain_goods_id and cgi.type=? ");
		sql.append(" left join chain c on cg.chain_id=c.id ");
		sql.append(" where cg.is_delete=? ");

		StatementParameter param = new StatementParameter();
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(chainGoods.getChainId())) {
			sql.append(" and cg.chain_id=? ");
			param.setString(chainGoods.getChainId());
		}

		if (StringUtils.isNotBlank(chainGoods.getName())) {
			sql.append(" and cg.name like ? ");
			param.setString("%" + chainGoods.getName() + "%");
		}

		if (StringUtils.isNotBlank(chainGoods.getChainBrandId())) {
			sql.append(" and cg.chain_brand_id=? ");
			param.setString(chainGoods.getChainBrandId());
		}

		if (StringUtils.isNotBlank(chainGoods.getChainTypeId())) {
			sql.append(" and cg.chain_type_id=? ");
			param.setString(chainGoods.getChainTypeId());
		}

		if (chainGoods.getStatus() != null) {
			sql.append(" and cg.status=? ");
			param.setInt(chainGoods.getStatus());
		}
		if(checkType!=null && checkType == Is.YES.toIntValue()){
			sql.append(" and cg.status != ?");
			param.setInt(ChainGoodsStatus.AUDIT.toIntStatus());
		}
		if (chainGoods.getType() != null) {
			sql.append(" and cg.type=? ");
			param.setInt(chainGoods.getType());
		}
		sql.append(" order by cg.created_time desc");

		return super.queryForPagingList(sql.toString(), ChainGoodsBO.class, param, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCount(ChainGoods chainGoods,Integer checkType) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(cg.id) from chain_goods cg ");
		sql.append(" left join chain_brand cb on cg.chain_brand_id=cb.id ");
		sql.append(" left join chain_type ct on cg.chain_type_id=ct.id ");
		sql.append(" left join chain_goods_images cgi on cg.id=cgi.chain_goods_id and cgi.type=? ");
		sql.append(" left join chain c on cg.chain_id=c.id ");
		sql.append(" where cg.is_delete=? ");

		StatementParameter param = new StatementParameter();
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(chainGoods.getChainId())) {
			sql.append(" and cg.chain_id=? ");
			param.setString(chainGoods.getChainId());
		}

		if (StringUtils.isNotBlank(chainGoods.getName())) {
			sql.append(" and cg.name like ? ");
			param.setString("%" + chainGoods.getName() + "%");
		}

		if (StringUtils.isNotBlank(chainGoods.getChainBrandId())) {
			sql.append(" and cg.chain_brand_id=? ");
			param.setString(chainGoods.getChainBrandId());
		}

		if (StringUtils.isNotBlank(chainGoods.getChainTypeId())) {
			sql.append(" and cg.chain_type_id=? ");
			param.setString(chainGoods.getChainTypeId());
		}

		if (chainGoods.getStatus() != null) {
			sql.append(" and cg.status=? ");
			param.setInt(chainGoods.getStatus());
		}
		if(checkType!=null && checkType == Is.YES.toIntValue()){
			sql.append(" and cg.status != ?");
			param.setInt(ChainGoodsStatus.AUDIT.toIntStatus());
		}
		if (chainGoods.getType() != null) {
			sql.append(" and cg.type=? ");
			param.setInt(chainGoods.getType());
		}
		sql.append(" order by cg.created_time desc");
		return super.queryForInt(sql.toString(), param);
	}
	
	
	@Override
	public String queryMaxCode() {
		String sql = "select ifnull(max(CONVERT(code,SIGNED)),0)  from chain_goods";
		StatementParameter param = new StatementParameter();
		return super.queryForString(sql, param);
	}
	
	
	@Override
	public ChainGoodsBO findByIdForOther(String id) {

		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" cg.`audit_fail_description`,cg.`code`,cg.updated_time,cg.id,cg.`name`,cg.price,cg.reference_price,cg.is_open_sku,cg.is_open_member,cg.is_free_shipping,cg.`status`,cg.is_recommend,cg.is_open_sell,cg.is_open_rebate,cg.profit,cg.postage_type,cg.postage_price,cg.type,");
		
		//stock by sku
		sql.append(" (case cg.is_open_sku ");
		sql.append(" when 0 then cg.stock ");
		sql.append(" when 1 then (select sum(csc.stock) from chain_sku_content csc where csc.chain_goods_id=cg.id)");
		sql.append(" end)");
		sql.append(" stock,");
		
		//price by sku
		sql.append(" (case cg.is_open_sku ");
		sql.append(" when 1 then (select case min(csc.price)=max(csc.price) when 0 then concat_ws('~',min(csc.price),max(csc.price)) when 1 then min(csc.price)  end  from chain_sku_content csc where csc.chain_goods_id=cg.id) ");
		sql.append(" end)");
		sql.append(" sku_range_price,");
		
		sql.append(" cb.`name` brand_name, ");
		sql.append(" ct.`name` type_name, ");
		sql.append(" c.`name` chain_name, ");
		sql.append(" cgi.file_name ");
		sql.append("  from chain_goods cg ");
		sql.append(" left join chain_brand cb on cg.chain_brand_id=cb.id ");
		sql.append(" left join chain_type ct on cg.chain_type_id=ct.id ");
		sql.append(" left join chain_goods_images cgi on cg.id=cgi.chain_goods_id and cgi.type=? ");
		sql.append(" left join chain c on cg.chain_id=c.id ");
		sql.append(" where cg.is_delete=? ");
		sql.append(" and cg.id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(id);

		return super.query(sql.toString(), ChainGoodsBO.class, param);
	}
	
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	@Override
	public boolean updateStatus(String id, Integer status) {
		String sql = "update chain_goods set status=?,updated_time=? where id=?";

		StatementParameter param = new StatementParameter();
		param.setInt(status);
		param.setDate(new Date());
		param.setString(id);

		return super.updateForBoolean(sql, param);
	}
	
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	@Override
	public boolean batchUpdateStatus(String[] ids, Integer status) {
		final StringBuffer sql = new StringBuffer();
		final StatementParameter param = new StatementParameter();
		sql.append("update chain_goods set status=? where 1=1 and");

		if (ids.length > 0) {

			sql.append(" id in (");
			param.setInt(status);
			for (int i = 0, len = ids.length; i < ids.length; i++) {
				sql.append("?");
				if (i < len - 1) {
					sql.append(",");
				}
				param.setString(ids[i]);
			}
			sql.append(")");

		}

		return super.updateForBoolean(sql.toString(), param);
	}
	
	
	@Override
	public List<ChainGoodsBO> findPageAll(ChainGoodsListBO chainGoodsListBO, int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" cg.`audit_fail_description`,cg.`code`,cg.updated_time,cg.id,cg.`name`,cg.price,cg.reference_price,cg.is_open_sku,cg.is_open_member,cg.is_free_shipping,cg.`status`,cg.is_recommend,cg.is_open_sell,cg.is_open_rebate,cg.profit,cg.postage_type,cg.postage_price,cg.type,");
		
		//stock by sku
		sql.append(" (case cg.is_open_sku ");
		sql.append(" when 0 then cg.stock ");
		sql.append(" when 1 then (select sum(csc.stock) from chain_sku_content csc where csc.chain_goods_id=cg.id)");
		sql.append(" end)");
		sql.append(" stock,");
		
		//price by sku
		sql.append(" (case cg.is_open_sku ");
		sql.append(" when 1 then (select case min(csc.price)=max(csc.price) when 0 then concat_ws('~',min(csc.price),max(csc.price)) when 1 then min(csc.price)  end  from chain_sku_content csc where csc.chain_goods_id=cg.id) ");
		sql.append(" end)");
		sql.append(" sku_range_price,");
		
		
		sql.append(" cb.`name` brand_name, ");
		sql.append(" ct.`name` type_name, ");
		sql.append(" c.`name` chain_name, ");
		sql.append(" cgi.file_name,");
		
		//systemType concat
		sql.append(" (select group_concat(st.`name`)  from chain_goods_system_type cgst inner join system_type st on cgst.system_type_id=st.id  where cgst.chain_goods_id=cg.id) systemType");
		
		
		sql.append("  from chain_goods cg ");
		sql.append(" left join chain_brand cb on cg.chain_brand_id=cb.id ");
		sql.append(" left join chain_type ct on cg.chain_type_id=ct.id ");
		sql.append(" left join chain_goods_images cgi on cg.id=cgi.chain_goods_id and cgi.type=? ");
		sql.append(" left join chain c on cg.chain_id=c.id ");
		sql.append(" where cg.is_delete=? and cg.type <> ?");

		StatementParameter param = new StatementParameter();
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsType.INTEGRAL.toIntStatus());
		
		if (StringUtils.isNotBlank(chainGoodsListBO.getChainId())) {
			sql.append(" and cg.chain_id=? ");
			param.setString(chainGoodsListBO.getChainId());
		}

		if (StringUtils.isNotBlank(chainGoodsListBO.getName())) {
			sql.append(" and cg.name like ? ");
			param.setString("%" + chainGoodsListBO.getName() + "%");
		}

		if (StringUtils.isNotBlank(chainGoodsListBO.getChainBrandId())) {
			sql.append(" and cg.chain_brand_id=? ");
			param.setString(chainGoodsListBO.getChainBrandId());
		}

		if (StringUtils.isNotBlank(chainGoodsListBO.getChainTypeId())) {
			sql.append(" and cg.chain_type_id=? ");
			param.setString(chainGoodsListBO.getChainTypeId());
		}

		if (chainGoodsListBO.getStatus() != null) {
			sql.append(" and cg.status=? ");
			param.setInt(chainGoodsListBO.getStatus());
		}
		if(StringUtils.isNotBlank(chainGoodsListBO.getShopId())){
			sql.append(" and cg.id not in (select chain_goods_id from shop_chain_goods where shop_id=?)");
			param.setString(chainGoodsListBO.getShopId());
		}
		
		if(chainGoodsListBO.getType() != null){
			sql.append(" and cg.type=?");
			param.setInt(chainGoodsListBO.getType());
		}
		
		sql.append(" order by cg.created_time desc");

		return super.queryForPagingList(sql.toString(), ChainGoodsBO.class, param, pageNumber, maxPageRow);
	}

	@Override
	public int getTotalCount(ChainGoodsListBO chainGoodsListBO) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(cg.id) from chain_goods cg ");
		sql.append(" left join chain_brand cb on cg.chain_brand_id=cb.id ");
		sql.append(" left join chain_type ct on cg.chain_type_id=ct.id ");
		sql.append(" left join chain_goods_images cgi on cg.id=cgi.chain_goods_id and cgi.type=? ");
		sql.append(" left join chain c on cg.chain_id=c.id ");
		sql.append(" where cg.is_delete=? ");

		StatementParameter param = new StatementParameter();
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());

		if (StringUtils.isNotBlank(chainGoodsListBO.getChainId())) {
			sql.append(" and cg.chain_id=? ");
			param.setString(chainGoodsListBO.getChainId());
		}

		if (StringUtils.isNotBlank(chainGoodsListBO.getName())) {
			sql.append(" and cg.name like ? ");
			param.setString("%" + chainGoodsListBO.getName() + "%");
		}

		if (StringUtils.isNotBlank(chainGoodsListBO.getChainBrandId())) {
			sql.append(" and cg.chain_brand_id=? ");
			param.setString(chainGoodsListBO.getChainBrandId());
		}

		if (StringUtils.isNotBlank(chainGoodsListBO.getChainTypeId())) {
			sql.append(" and cg.chain_type_id=? ");
			param.setString(chainGoodsListBO.getChainTypeId());
		}

		if (chainGoodsListBO.getStatus() != null) {
			sql.append(" and cg.status=? ");
			param.setInt(chainGoodsListBO.getStatus());
		}
		
		if(StringUtils.isNotBlank(chainGoodsListBO.getShopId())){
			sql.append(" and cg.id not in (select chain_goods_id from shop_chain_goods where shop_id=?)");
			param.setString(chainGoodsListBO.getShopId());
		}
		
		if(chainGoodsListBO.getType() != null){
			sql.append(" and cg.type=?");
			param.setInt(chainGoodsListBO.getType());
		}

		return super.queryForInt(sql.toString(), param);
	}
	
}
